#!/bin/perl



#
#D:\dragon boat\2009\Regression>db2 "select * from regattas"
#
#NAME                      REGRESS_ORDER
#------------------------- -------------
#GWN Sport                             2
#Pickering                             1
#TIDBRF                                0
#
#  3 record(s) selected.
#
#db2 "update regattas set REGRESS_ORDER = REGRESS_ORDER + 1 where REGRESS_ORDER >= 1"
#db2 insert into REGATTAS values ('Milton', 1)



# Careful with opening CSV files in excel.  It will change the format of my times to mm:ss.0 so I need
# to change it back to mm:ss.00 before saving or else I will lose the 1/100 of a seconds column

system("processCSV.pl regressionCenterIslandSaturday.csv");
system("processCSV.pl regressionCenterIslandSunday.csv");
system("processCSV.pl regressionGWNSport.csv");
system("processCSV.pl regressionPickeringSaturday.csv");
system("processCSV.pl regressionPickeringSunday.csv");
system("processCSV.pl regressionMilton.csv");
system("processCSV.pl regressionWelland.csv");
system("processCSV.pl regressionHamilton.csv");
system("processCSV.pl regressionHamilton.csv");
system("processCSV.pl regressionOHDBC3K.csv");
system("processCSV.pl regressionMontrealSaturday.csv");
system("processCSV.pl regressionMontrealSunday.csv");
system("processCSV.pl regressionOttawaSaturday.csv");
system("processCSV.pl regressionOttawaSunday.csv");
system("processCSV.pl regressionWaterloo.csv");
system("processCSV.pl regressionWoodstock.csv");
system("processCSV.pl regressionBelleville.csv");
system("processCSV.pl regressionLachine.csv");
system("processCSV.pl regressionParrySound.csv");
system("processCSV.pl regressionHuntsville.csv");
system("processCSV.pl regressionNDC.csv");
system("processCSV.pl regressionOakville.csv");
system("processCSV.pl regressionNationals.csv");
system("processCSV.pl regressionGWNC.csv");
system("processCSV.pl regressionStratford.csv");
system("processCSV.pl regressionQC.csv");

system("db2 connect to dboat");
system("db2 drop table race");
system("db2 create table race (TEAM VARCHAR(100), CATEGORY VARCHAR(100), TIME DECIMAL(31,20), RACENO INTEGER, DISTANCE INTEGER, REGATTA VARCHAR(25), YEAR INTEGER, LANE INTEGER, RACENAME VARCHAR(50) WITH DEFAULT) ");

system("db2 load from regressionCenterIslandSaturday.csv.out of del insert into race");
system("db2 load from regressionCenterIslandSunday.csv.out of del insert into race");
system("db2 load from regressionGWNSport.csv.out of del insert into race");
system("db2 load from regressionPickeringSaturday.csv.out of del insert into race");
system("db2 load from regressionPickeringSunday.csv.out of del insert into race");
system("db2 load from regressionMilton.csv.out of del insert into race");
system("db2 load from regressionWelland.csv.out of del insert into race");
system("db2 load from regressionHamilton.csv.out of del insert into race");
system("db2 load from regressionOHDBC3K.csv.out of del insert into race");
system("db2 load from regressionMontrealSaturday.csv.out of del insert into race");
system("db2 load from regressionMontrealSunday.csv.out of del insert into race");
system("db2 load from regressionOttawaSaturday.csv.out of del insert into race");
system("db2 load from regressionOttawaSunday.csv.out of del insert into race");
system("db2 load from regressionWaterloo.csv.out of del insert into race");
system("db2 load from regressionWoodstock.csv.out of del insert into race");
system("db2 load from regressionBelleville.csv.out of del insert into race");
system("db2 load from regressionLachine.csv.out of del insert into race");
system("db2 load from regressionParrySound.csv.out of del insert into race");
system("db2 load from regressionHuntsville.csv.out of del insert into race");
system("db2 load from regressionNDC.csv.out of del insert into race");
system("db2 load from regressionOakville.csv.out of del insert into race");
system("db2 load from regressionNationals.csv.out of del insert into race");
system("db2 load from regressionGWNC.csv.out of del insert into race");
system("db2 load from regressionStratford.csv.out of del insert into race");
system("db2 load from regressionQC.csv.out of del insert into race");


#May ?  OHDBC3K
#May 23 Milton
#June 6 Pickering
#June 7 Pickering HS
#June 13 Welland, Parry Sound, Lachine
#June 21 TIDBRF,  Ottawa
#July 4 Hamilton, Belleville sprints
#July 18 GWN Sport, Waterloo
#July 25, 26 Montreal
#Aug 8 Woodstock, Huntsville
#Aug 15 NDC
#Aug 16 Oakville
#Aug 22 Nationals
#Sep 19 Stratford
#Sep 19 QC
system("db2 drop table regattas");
system("db2 create table regattas (NAME VARCHAR(25), REGRESS_ORDER INTEGER, PRINT_ORDER INTEGER, WHEN DATE, WEBSITE VARCHAR(100), COMPETITIVE DECIMAL(4,2))");
# ---------------------- 500m regression order ----------------------
# -1 for regress_order means it will not be included in the regression
system("db2 insert into regattas values " .
            " ('OHDBC3K',     -1, -1,  DATE('2009-05-30'), 'http://www.ohdbc.ca/', 0), " . 
            " ('TIDBRF',       0,  4,  DATE('2009-06-21'), 'http://www.dragonboats.com/', 0), " .
            " ('Milton',       1,  0,  DATE('2009-05-23'), 'http://www.dragon-boats.com/milton/', 0),  ".
            " ('Pickering',    2,  1,  DATE('2009-06-06'), 'http://www.pdbc.ca/', 0),  ".
            " ('PickeringHS',  3,  2,  DATE('2009-06-06'), 'http://www.pdbc.ca/', 0), ". 
            " ('Hamilton',     4,  6,  DATE('2009-07-04'), 'http://www.dragon-boats.com/hamilton/index.html', 0), ".
            " ('Welland',      5,  3,  DATE('2009-06-13'), 'http://www.wellanddragonboatfestival.com/', 0), ".
            " ('Parry Sound', -1, -1,  DATE('2009-06-13'), 'http://www.dragonboatfestival.org/', 0), ".
            " ('Lachine',     -1, -1,  DATE('2009-06-13'), 'http://www.22dragons.com/site/?q=en/node/49', 0), ".
            " ('Belleville',  -1, -1,  DATE('2009-07-04'), 'http://www.alkame.ca/bellevillesprints09.html', 0), ".
            " ('GWN Sport',    6,  7,  DATE('2009-07-18'), 'http://www.gwndragonboat.com/Default.asp?id=16&l=1', 0), ".
            " ('Waterloo',     7,  8,  DATE('2009-07-18'), 'http://www.waterloodragonboat.org/', 0), ".
            " ('Montreal',     8,  9,  DATE('2009-07-25'), 'http://www.montrealdragonboat.com/', 0), " .
            " ('Ottawa',       9,  5,  DATE('2009-06-21'), 'http://www.dragonboat.net/', 0), " . 
            " ('Woodstock',    10, 10, DATE('2009-08-08'), 'http://www.dragonboatwoodstock.ca', 0), " .
            " ('Huntsville',  -1, -1,  DATE('2009-08-08'), 'http://www.huntsvilledragonboat.com/', 0), "  .
            " ('NDC',         11, 11,  DATE('2009-08-15'), 'http://www.nationaldragonboat.com/', 0), " .
            " ('Oakville',    12, 12,  DATE('2009-08-16'), 'http://www.oakvilledragon.com/', 0), " .
            " ('Nationals',   13, 13,  DATE('2009-08-22'), 'http://www.dragonboat.ca/node/116', 0), " .
            " ('GWNC',        14, 14,  DATE('2009-09-12'), 'http://www.gwndragonboat.com/Default.asp?id=17&l=1', 0), " .
            " ('Stratford',   15, 15,  DATE('2009-09-19'), 'http://www.rotarystratford.com/dragonboat/', 0), " .
            " ('QC',          16, 16,  DATE('2009-09-19'), 'http://www.22dragons.com/', 0) "
      ); 



# ---------------------- 200m regression order ----------------------
#system("db2 insert into regattas values ('Pickering', 0, 0, 1.0), ('GWN Sport', 1, 2, 1.0), ('Montreal', 2, 3, 1.0), ('Ottawa', 3, 1, 1.0), ('Lachine', 4, 4, 1.0), ('Belleville', 5, 5, 1.0)"); 


# ---------------------- 2000m regression order ----------------------
#system("db2 insert into regattas values ('Pickering', 0, 0, 1.0), ('GWN Sport', 1, 1, 1.0), ('Woodstock', 2, 2, 1.0), ('Milton', 3, 3, 1.0), ('Hamilton', 4, 4, 1.0),  ('Belleville', 5, 5, 1.0)");


system("db2 export to race.del of del select * from race");
system("db2 export to dates.del of del select distinct(race.regatta), regattas.regress_order, regattas.print_order, regattas.when, regattas.website, regattas.competitive from race, regattas where race.regatta=regattas.name");


system("db2 update race set team='MOFOS' where team like 'NAPA/%'");
system("db2 connect");
system("db2 update race set team='MOFOS' where team like 'NAPA%'");
system("db2 delete from race where team like 'ON S''EN CALISSE'");
system("db2 delete from race where team like 'LANE 6 7 8 9 AND 10'");
system("db2 delete from race where team like 'SRP' and regatta like 'NDC' and raceNo=33");


system("db2 connect");
system("db2 connect reset");



# update Vic college pickerint 2:22 to 2:17



